<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/**
 *
 *
 */
class json extends CI_Model  {
	/**
	 * Constructor
	 * @access protected
	 */
	function _construct()
	{
		// Call the Model constructor
		parent::_construct();
	}
	function gethotelfull($id)
	{
		$query='select `desc` from service where id='.$id;
		$result=$this->db->query($query);
		$responce=array();
		if($result->num_rows>0)
		{
			$responce['description']=$result->row()->desc;
		}
		else
		{
			$responce['description']="No description";
		}
		return json_encode($responce);
	}
	function gethotellist($client,$agent,$type)
	{
		$query='select idUser from podpisannie where idUser='.$agent.' and idPodp='.$client;
		$result=$this->db->query($query);
		$responce=array();
		$hotels=array();
		if($result->num_rows>0||$agent==$client)
		{
			$query='select s.id,s.name, o.`desc`,o.idoption
						from service as s
			            join  listoptions as o
			                on o.idservice=s.id and (o.idoption=13 or o.idoption=14 or o.idoption=15 or o.idoption=54 or o.idoption=55)
			            left join listofservices as ls on s.id=ls.idService
									where  ls.idUser='.$agent.'
										and s.idtype=1 and s.`show`=1  order by s.name asc';
			$result=$this->db->query($query);
			foreach($result->result() as $row)
			{
				if(!isset($responce[$row->id]))
				{
					$responce[$row->id]['name']=$row->name;
					$responce[$row->id]['id']=$row->id;
					//$query='SELECT min(price) as mn FROM price where idRow=1 and idtable in (select id from listoftables where idservice='.$row->id.') and left(`Date`,7)="'.date('Y-m').'" limit 1';
					//$price=$this->db->query($query);
					//if($price->num_rows()>0)
					//{
						$responce[$row->id]['min_p']=0;
							
					//}
					$responce[$row->id]['category']='';
					$responce[$row->id]['city']='';
					$responce[$row->id]['coordinates']='';
					$responce[$row->id]['dblchld']=0;
					$responce[$row->id]['chldage']=0;
						
				}
					
				if($row->idoption==14)
				{
					$responce[$row->id]['category']=$row->desc;
				}
				elseif($row->idoption==13)
				{
					$query='select id,name from service where id='.$row->desc;
					$resp=$this->db->query($query);
					if($resp->num_rows()>0)
					{
						$responce[$row->id]['city']=$resp->row()->name;
						$responce[$row->id]['idcity']=$resp->row()->id;
					}
					else
					{
						$responce[$row->id]['city']='no city';
						$responce[$row->id]['idcity']=0;
					}
				
				}
				elseif($row->idoption==15)
				{
					$responce[$row->id]['coordinates']=$row->desc;
				}
				elseif($row->idoption==54)
				{
					$responce[$row->id]['dblchld']=$row->desc;
				}
				else
				{
					$responce[$row->id]['chldage']=$row->desc;
				}
			}
			
			
		}
	
		return json_encode($responce);
	}
	function price($date1,$date2,$oto,$type)	{
		$responce=array();		$dateA=explode('-',$date1);		$dateB=explode('-',$date2);		$days=(mktime(0,0,0,$dateB[1],$dateB[2],$dateB[0])-mktime(0,0,0,$dateA[1],$dateA[2],$dateA[0]))/ 86400;		
		$date1 = date('Y-m-d',mktime(0, 0, 0, $dateA[1]  , $dateA[2]+1, $dateA[0]));
		$query="SELECT p.price as price,s.id,p.`date`,l.desc
				FROM price p
				JOIN listoftables l
				     on l.id=p.idTable
				inner JOIN listofservices as l2
				     on l2.idUser=$oto
				inner JOIN service as s
				     on s.id = l2.idService
				     and l.idservice=s.id
				     and s.idtype=$type
				WHERE	p.`Date`	between STR_TO_DATE('$date1', '%Y-%m-%d') AND STR_TO_DATE('$date2', '%Y-%m-%d')
				  and p.idRow=1";
				$temp=array();
		$result=$this->db->query($query);
		foreach($result->result() as $row)		
		{			
			preg_match("/^(\d)\-(\d)/", $row->desc, $tmp); 			
			preg_match("/^(\d)\+/", $row->desc, $tmp2);			
			if(count($tmp)>0)			
			{				
				if($tmp[1]<=$days and $tmp[2]>=$days)				
				{					
					if(isset($temp[$row->id][$row->date]))					
					{						
						if($temp[$row->id][$row->date]>$row->price)						
						{							
							$temp[$row->id][$row->date]=$row->price;						
						}					
					}					
					else					
					{						
						$temp[$row->id][$row->date]=$row->price;					
					}				
				}			
			}			
			elseif(count($tmp2)>0)			
			{				
				if($days>=$tmp2[1])				
				{					
					if(isset($temp[$row->id][$row->date]))				
					{						
						if($temp[$row->id][$row->date]>$row->price)						
						{							
							$temp[$row->id][$row->date]=$row->price;						
						}					
					}					
					else					
					{						
						$temp[$row->id][$row->date]=$row->price;					
					}				
				}			
			}			
			else			
			{				
				if(isset($temp[$row->id][$row->date]))			
				{					
					if($temp[$row->id][$row->date]>$row->price)					
					{						
						$temp[$row->id][$row->date]=$row->price;					
					}				
				}				
				else				
				{					
					$temp[$row->id][$row->date]=$row->price;				
				}			
			}
		}		
		foreach($temp as $key=>$val)
		{
			$sum=0;
			foreach($val as $r)
			{
				$sum+=$r;
			}
			$responce[$key]=$sum*2;
		}	
		return json_encode($responce);
		/*$query='select idUser from podpisannie where idUser='.$agent.' and idPodp='.$client;
		$result=$this->db->query($query);
		
		$hotels=array();
		if($result->num_rows>0||$agent==$client)
		{
			
		}*/
		
	}
	function dates($id)
	{
		$responce=array();
		$query="SELECT `date` FROM `price`
				inner join listoftables as l on idTable=l.id and idservice=$id
				where `date`>NOW()
				group by `date`
				order by `date`";
		$result=$this->db->query($query);
		foreach($result->result() as $row)
		{
			$responce[]=$row->date;
		}		
		return json_encode($responce);
	}
	function t_price($date,$oto,$type)
	{
		$responce=array();
		$query='SELECT p.price AS price, s.id, p.idrow,l.name
			FROM price p
			JOIN listoftables l ON l.id = p.idTable
			LEFT JOIN listofservices AS l2 ON l2.idUser ='.$oto.'
			LEFT JOIN service AS s ON s.id = l2.idService
			AND s.idtype ='.$type.'
			WHERE p.`Date` =  "'.$date.'"
			AND l.idservice = s.id';
		
		$result=$this->db->query($query);
		foreach($result->result() as $row)
		{
			$responce[$row->id][$row->name][$row->idrow]=$row->price;
		}
		return json_encode($responce);
	}
	function getlist($agent,$client,$type)
	{
		$option=array(
					3=>array('nights'=>6,'city'=>31,'hotel'=>'8','proj'=>'1'),
					1=>array('nights'=>14,'city'=>13,'hotel'=>'8','proj'=>'1'),
					2=>array('nights'=>30,'city'=>29,'hotel'=>'8','proj'=>'1')
					);
		$responce=array();
		// название + описание
		$query='SELECT s.id, s.name, s.`desc` 
					FROM service AS s
					INNER JOIN podpisannie AS p 
						ON s.iduser = p.idUser
						AND (
							IF( '.$client.' = '.$agent.', p.idUser ='.$client.', 0 ) 
							OR 
							(p.idUser ='.$client.'	AND p.idpodp ='.$agent.')
						)
					WHERE s.idtype =3 and s.`show`=1
					GROUP BY s.id';
		$result=$this->db->query($query);
		$ids=array();
		foreach($result->result() as $row)
		{
			$responce[$row->id]['name']=$row->name;
			$responce[$row->id]['desc']=$row->desc;
			$responce[$row->id]['nights']='';
			$responce[$row->id]['proj']='';
			$responce[$row->id]['city']=array();
			$responce[$row->id]['excurs']=array();
			$responce[$row->id]['hotel']=array();
			$responce[$row->id]['min_p']=0;
			$responce[$row->id]['category']=array();
			$responce[$row->id]['foto']=array();
			$ids[]=$row->id;
		}

		// категории
		$query='SELECT l.name,s.id from listoftables as l 
				inner join service as s on s.iduser='.$client.' and s.idtype=3 and l.idservice=s.id';
		$result=$this->db->query($query);
		$categories=array();
		foreach($result->result() as $row)
		{
			$name=$row->name;
			$responce[$row->id]['category'][]=$row->name;
			if(!in_array($name,$categories))
			$categories[]=$row->name;
		}
		$responce['categories']=$categories;
		
		// основная информация по турам
		$query='SELECT o.idOption, o.`desc` , o.idservice
			FROM listoptions AS o
			WHERE idservice
				IN ( '.implode(', ',$ids).' ) 
			AND idoption
				IN ( 1, 2, 6, 8, 31 ) ';
		$result=$this->db->query($query);
		
		$excurs=array();
		$city=array();
		$hotel=array();
		$spisexcurs=array();
		$spiscity=array();
		$spishotel=array();
		
		foreach ($result->result() as $row)
		{
			switch($row->idOption)
			{
				case 1: // проживание
					$responce[$row->idservice]['proj']=$row->desc;
				break;
				case 2: // экскурсия
					$excurs[$row->desc][]=$row->idservice;
					//!in_array($row->desc,$spisexcurs[])?$spisexcurs[]=$row->desc:'';
					if(!in_array($row->desc,$spisexcurs))
					$spisexcurs[]=$row->desc;
				break;
				case 6: // ночи
					$responce[$row->idservice]['nights']=$row->desc;
				break;
				case 8: // отель
					if($row->desc!='')
					{
						$hotel[$row->desc][]=$row->idservice;
						if(!in_array($row->desc,$spishotel))
							$spishotel[]=$row->desc;
					}
				break;
				case 31: // город
					$city[$row->desc][]=$row->idservice;
					if(!in_array($row->desc,$spiscity))
					$spiscity[]=$row->desc;
				break;
			}
		}
		
		$str=array();
		if(count($spiscity)>0){
			$str[]=implode(',',$spiscity);
		}
		if(count($spishotel)>0){
			$str[]=implode(',',$spishotel);
		}
		if(count($spisexcurs)>0){
			$str[]=implode(',',$spisexcurs);
		}
		
		if(count($str)>0)
		{
			$query='SELECT s.id, s.name, s.idtype,o.`desc`,o.idoption
					FROM service as s
					left join listoptions as o on (o.idoption=14 or o.idoption=13)  and o.idservice=s.id
					WHERE s.id
					IN ( '.implode(',',$str).')';
			$result=$this->db->query($query);
			
			foreach($result->result() as $row)
			{
				switch($row->idtype)
				{
					case 1: //hotels
						foreach($hotel[$row->id] as $ci)
						{
							$responce[$ci]['hotel'][$row->id]['name']=$row->name;
							if($row->idoption==13)
							{
								$responce[$ci]['hotel'][$row->id]['city']=$row->desc;
							}else
							{
								$responce[$ci]['hotel'][$row->id]['category']=$row->desc;
							}
						}
						
					break;
					case 2: //excursion
						foreach($excurs[$row->id] as $ci)
						{
							$responce[$ci]['excurs'][$row->id]=$row->name;
						}
					break;
					case 5:  //city
						foreach($city[$row->id] as $ci)
						{
							$responce[$ci]['city'][$row->id]=$row->name;
						}
					break;
				}
			}
		}
		
		// фотографии
		$query="SELECT f.url, l.idservice
		FROM fotos AS f
		INNER JOIN listfotos AS l ON l.idfoto = f.id
		AND l.idservice
		IN (".implode(', ',$ids)." ) ";
		$result=$this->db->query($query);
		foreach($result->result() as $row){
			$responce[$row->idservice]['foto'][]=$row->url;
		}
		
		return json_encode($responce);
	}

	function getfull($type,$id)
	{
		switch($type)
		{
			case 3:
				$responce=array();
				#Hotels name
				$query="select id,name from service where id in (select `desc` from listoptions where idservice=".$id." and idoption=8)";
				$this->log($query);
				$result=$this->db->query($query);
				foreach($result->result() as $row)
				{
					$responce['hotel'][$row->id]=$row->name;
				}
				#Exkursions
				$query="select id,name from service where id in (select `desc` from listoptions where idservice=".$id." and idoption=2)";
				$this->log($query);
				$result=$this->db->query($query);
				foreach($result->result() as $row)
				{
					$responce['excurs'][$row->id]=$row->name;
				}
				# get full description
				$query='select `desc` from service where id='.$id;
				$result=$this->db->query($query);
				$responce['description']=$result->row()->desc;
				//return $responce;
				return json_encode($responce);
			break;
			case 1:
				$responce=array();
				#description and all options
				$query="select `desc` from service where id=".$id;
				$result=$this->db->query($query);
				$responce['description']=$result->row()->desc;
				#select all options from this service
				$query="SELECT idoption, `desc` FROM listoptions l where idservice=".$id;
				$result=$this->db->query($query);
				$responce['in_h_ico']="";
				$responce['in_r_ico']="";

				foreach($result->result() as $row)
				{
					switch($row->idoption)
					{
						case '15':
							$responce['place']=$row->desc;
						break;
						case '17':
							$responce['in_hotel']=$row->desc;
						break;
						case '19':
							$responce['in_room']=$row->desc;
						break;
						case '16':
							$query="select `name`,`rules` from options where id=".$row->desc;
							$res=$this->db->query($query);
							foreach($res->result() as $r)
							{
								$responce['in_h_ico'][]=$r->name;
							}
						break;
						case '18':
							$query="select `name`,`rules` from options where id=".$row->desc;
							$res=$this->db->query($query);
							foreach($res->result() as $r)
							{
								$responce['in_r_ico'][]=$r->name;
							}
						break;
					}
					//$responce['excurs'][$row->id]=$row->name;
				}

				//return $responce;
				return json_encode($responce);
			break;
			case 5:
				$responce=array();
				#description and all options
				$query="select `desc` from service where id=".$id;
				$result=$this->db->query($query);
				$responce['description']=$result->row()->desc;
				#select all options from this service
				$query="SELECT url FROM fotos where id in (select idfoto from listfotos where idservice=".$id." )";
				$result=$this->db->query($query);
				$responce['foto']=array();
				foreach($result->result() as $row)
				{
					$responce['foto'][]=$row->url;
				}
				return json_encode($responce);
				
			break;
			case 2:
				$responce=array();
				#description and all options
				$query="select `desc` from service where id=".$id;
				$result=$this->db->query($query);
				$responce['description']=$result->row()->desc;
				#select all options from this service
				$query="SELECT url FROM fotos where id in (select idfoto from listfotos where idservice=".$id." )";
				$result=$this->db->query($query);
				$responce['foto']=array();
				foreach($result->result() as $row)
				{
					$responce['foto'][]=$row->url;
				}
				$query='Select id,name from service where id in (select `desc` from listoptions where idservice='.$id.' and idoption=30)';
				$result=$this->db->query($query);
				$responce['showplace']=array();
				foreach($result->result() as $row)
				{
					$responce['showplace'][$row->id]=$row->name;
				}
				return json_encode($responce);
			
			break;
			case 4:
				$responce=array();
				#description and all options
				$query="select `desc` from service where id=".$id;
				$result=$this->db->query($query);
				$responce['description']=$result->row()->desc;
				#select all options from this service
				$query="SELECT url FROM fotos where id in (select idfoto from listfotos where idservice=".$id." )";
				$result=$this->db->query($query);
				$responce['foto']=array();
				foreach($result->result() as $row)
				{
					$responce['foto'][]=$row->url;
				}
				
				return json_encode($responce);
					
				break;
		}
	}
	function get_foto($id)
	{
		$query="select url from fotos where id in (select idfoto from listfotos where idservice=".$id.")";
		$result=$this->db->query($query);
		$responce=array();
		foreach($result->result() as $row)
		{
			$responce[]=$row->url;
		}
		return json_encode($responce);
	}
	function get($y,$m,$agent,$id)
	{
		$responce=array();
		$n=($m!=2?(($m%2)^($m>7))+30:(!($y%400)||!($y%4)&&($y%25)?29:28));
		$dateS=$y."-".$m."-01";
		$dateE=$y."-".$m."-".$n;

	/*	$query="select name from service where id=".$id;
		$result=$this->db->query($query);
		if($result->num_rows()>0)
		{
			$name=$result->row()->name;
		}
		else
		{
			die('hack');
		}
		$responce['serviceName']=$name;*/
		$responce['serviceId']=$id;
		$query="Select id,idStruct,name,`desc` from listoftables where idService=".$id;
		$result=$this->db->query($query);
		$table=0;
		foreach($result->result() as $row)
		{

			$idstruct=$row->idStruct;
			$tableName=$row->name;
			$idtable=$row->id;
			$responce['tables'][$table]['name']=$tableName;
			$responce['tables'][$table]['desc']=$row->desc;
			$responce['tables'][$table]['id']=$idtable;

			$query="Select idrow from liststruct where idStruct=".$idstruct;
			$result=$this->db->query($query);
			$i=0;
			foreach($result->result() as $row)
			{
				$query="Select id,name,`desc` from rows where id=".$row->idrow;
				$rowsName=$this->db->query($query);
				if($rowsName->num_rows()>0)
				{
					$row=$rowsName->row();
					$struct[$i]['id']=$row->id;
					$struct[$i]['name']=$row->name;
					$struct[$i]['desc']=$row->desc;
					$i++;
				}
			}
			//$query='select name from '
			$query="Select `Date`,`price`,`idRow` from price where idTable=".$idtable." and `date`>='$dateS' and `date`<='$dateE'";
			$result=$this->db->query($query);

			$DATA=array();
			$i=0;
			foreach($result->result() as $row)
			{
				$DATA[$i]['price']=$row->price;
				$DATA[$i]['date']=$row->Date;
				$DATA[$i]['idRow']=$row->idRow;
				$i++;
			}
			$rows=array();

			for($i=0;$i<count($struct);$i++)
			{
				$rows[$i]['cell']=array($struct[$i]['id'],$struct[$i]['name'],$struct[$i]['desc']);
				for($j=3;$j<=$n+2;$j++)
				{
					if($price=$this->_search($j-2,$m,$y,$struct[$i]['id'],$DATA))
					{
						$rows[$i]['cell'][$j]=$price==0?"":$price;
					}
					else
					{
						$rows[$i]['cell'][$j]="";
					}
				}
			}
			$responce['tables'][$table]['rows']=$rows;
			$table++;
		}

		return json_encode($responce);
	}
	function get2($dateS,$dateE,$id)
	{
		$query="Select id,name,`desc` from rows ";
		$rowsName=$this->db->query($query);
		$rowsStruct=array();
		foreach($rowsName->result() as $row)
		{
			$rowsStruct[$row->id]['name']=$row->name;
			$rowsStruct[$row->id]['desc']=$row->desc;
		}
		$responce=array();
		$responce['serviceId']=$id;
		$query="Select id,idStruct,name,`desc` from listoftables where idService=".$id;
		$result=$this->db->query($query);
		$table=0;
		$query="Select idStruct,idRow from liststruct where ";
		$str=array();
		$str2=array();
		foreach($result->result() as $row)
		{
			$idstruct=$row->idStruct;
			$tableName=$row->name;
			$idtable=$row->id;
			$responce['tables'][$idtable]['name']=$tableName;
			$responce['tables'][$idtable]['desc']=$row->desc;
			$responce['tables'][$idtable]['idStruct']=$idstruct;
			$responce['tables'][$idtable]['id']=$idtable;
			$str[]=' idStruct='.$idstruct;
			$str2[]=' idTable='.$idtable;
		}
		$query.=implode(' or ',$str);
		$result=$this->db->query($query);
		$i=0;
		foreach($result->result() as $row)
		{
			$struct[$row->idStruct][$row->idRow]['name']=$rowsStruct[$row->idRow]['name'];
			$struct[$row->idStruct][$row->idRow]['desc']=$rowsStruct[$row->idRow]['desc'];
			$i++;
		}
		$query="Select `Date`,`price`,`idRow`,idTable from price where `date`>='$dateS' and `date`<='$dateE' and";
		$query.='('.implode(' or ',$str2).')';
		$result=$this->db->query($query);
		$DATA=array();
		foreach($result->result() as $row)
		{
			$DATA[$row->idTable][$row->Date][$row->idRow]['price']=$row->price;
		}
		list($ys,$ms,$ds) = explode("-",$dateS);
		$time1 = mktime(0,0,0,$ms,$ds,$ys);
		list($ye,$me,$de) = explode("-",$dateE);
		$time2 = mktime(0,0,0,$me,$de,$ye);
		$n=($ms!=2?(($ms%2)^($ms>7))+30:(!($ys%400)||!($ys%4)&&($ys%25)?29:28));
		
		
		foreach($responce['tables'] as $idTable=>$table)
		{
			$rows=array();
			foreach($struct[$table['idStruct']] as $idRow=>$row)
			{
				$j=3;
				$responce['tables'][$idTable]['rows'][$idRow]['cell']=array($idRow,$row['name'],$row['desc']);
				for ($i = $time1; $i <= $time2; $i+=24*60*60)
				{
					$responce['tables'][$idTable]['rows'][$idRow]['cell'][$j]=0;
					$j++;
				}
			}
		}
		foreach($DATA as $idTable=>$date)
		{
			foreach($date as $md=>$row)
			{
				list($y,$m,$d) = explode("-",$md);
				$time = mktime(0,0,0,$m,$d,$y);
				$d=(int)($time-$time1)/(24*60*60);
				foreach($row as $id=>$price)
				{
					$responce['tables'][$idTable]['rows'][$id]['cell'][3+$d]=$price['price'];
				}
			}
				
		}
		return json_encode($responce);
	}
	function _search($dd,$mm,$yyyy,$idRow,$DATA)
	{
		$date=$yyyy;
		if (strlen ($mm)<2) {
			$date.="-0".$mm;
		}
		else
		{
			$date.="-".$mm;
		}
		if (strlen ($dd)<2) {
			$date.="-0".$dd;
		}
		else
		{
			$date.="-".$dd;
		}
		for ($i=0;$i<count($DATA);$i++)
		{
			if(strcmp ($DATA[$i]['date'],$date)==0&&$DATA[$i]['idRow']==$idRow)//
			{
				return $DATA[$i]['price'];
			}
		}
		return false;
	}
	function search_date($date,$id)
	{
		$responce=array();
		$query="SELECT p.`date` 
				FROM price AS p
				INNER JOIN listoftables AS l ON l.idService =".$id."
				AND l.id = p.idTable
				WHERE p.`date` >  '".$date."'
				ORDER BY p.`date` ASC 
				LIMIT 1";
		$result=$this->db->query($query);
		if($result->num_rows()>0)
		{
		$responce['date']=$result->row()->date;
		}
		else
		{
			$responce['date']='';
		}
		return json_encode($responce);
	}
	/*function explode()
	{
	
		$query='select id,name,`desc` from service where id in (39,40,41)';
		$result=$this->db->query($query);
		foreach($result->result() as $row)
		{
			$service[$row->id]['name']=$row->name;
			$service[$row->id]['desc']=$row->desc;
		}
		foreach($service as $id=>$value)
		{
			$query='SELECT id, name,idStruct
			FROM listoftables
			WHERE idservice ='.$id;
			$result=$this->db->query($query);
			foreach($result->result() as $row)
			{
				$query='insert into service (name,`desc`,iduser,idtype) values ("'.$value['name'].' '.$row->name.'","'.$value['desc'].' &nbsp;",17,3)';
				$this->db->query($query);
				$sid=mysql_insert_id();
				$query='insert into listofservices (idUser,idService) values (17,'.$sid.')';
				$this->db->query($query);
				//$query='insert into listoftables (Name,`Desc`,idStruct,idService ) values ("1","2",'.$row->idStruct.','.$sid.')';
				//$this->db->query($query);
				//$sid=mysql_insert_id();
				//$query='update price set idTable='.$sid.' where idTable='.$row->id;
				//$this->db->query($query);
				$query='update listoftables set idService='.$sid.' where id='.$row->id;
				$this->db->query($query);
				
			}
		}
	}*/
}